# coding=utf-8
import requests
import json
import time
import pymysql
from config import db_host, db_port
import redis
import random
from playwright.sync_api import sync_playwright

conn = pymysql.connect(host=db_host,
                       user='root',
                       port=db_port,
                       password='xhhg@2020',
                       database='big_data',
                       cursorclass=pymysql.cursors.DictCursor)
# redis_con = redis.Redis(
#     host='121.4.69.146', password='xhhg123456', port=6389, db=2, decode_responses=True)
redis_con = redis.Redis(host='127.0.0.1',
                        port=6379, db=2, decode_responses=True)
cur = conn.cursor()
now_time = '0619'


def create_table(now_time):
    # redis创建需查询的专利号
    create_sql_payment = f"CREATE TABLE If Not Exists `by_payment_information_{now_time}` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id', `cost_category` varchar(50) DEFAULT NULL COMMENT '缴费种类', `payment_amount` varchar(50) DEFAULT NULL COMMENT '缴费金额', `pay_date` datetime DEFAULT NULL COMMENT '缴费日期', `name_payer` varchar(50) DEFAULT NULL COMMENT '缴费人', `note_the_code` varchar(50) DEFAULT NULL COMMENT '票据代码', `ticket_number` varchar(50) DEFAULT NULL COMMENT '票据号码', `create_time` datetime DEFAULT NULL COMMENT '插入时间', `updata_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更改时间', `application_number` varchar(50) DEFAULT NULL COMMENT '申请号', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;"
    cur = conn.cursor()
    cur.execute(create_sql_payment)
    conn.commit()

    create_sql_annual = f"CREATE TABLE If Not Exists `annual_fee_information_{now_time}` (`id` int(11) NOT NULL AUTO_INCREMENT, `application_number` varchar(255) DEFAULT NULL, `applicant` varchar(255) DEFAULT NULL COMMENT '专利权人', `money_type` varchar(255) DEFAULT NULL, `money` varchar(255) DEFAULT NULL, `close_date` varchar(255) DEFAULT NULL, `agency` varchar(255) DEFAULT NULL COMMENT '代理机构', `pay_status` varchar(50) DEFAULT '0', `created_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, `upload_port` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;"
    cur.execute(create_sql_annual)
    conn.commit()

    create_sql_overdue = f"CREATE TABLE If Not Exists `overdue_fine_{now_time}` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id', `application_number` varchar(50) DEFAULT NULL COMMENT '申请号', `payment_start_time` datetime DEFAULT NULL COMMENT '滞纳金开始时间', `payment_end_time` datetime DEFAULT NULL COMMENT '滞纳金结束时间', `current_annual_fee` varchar(20) DEFAULT NULL COMMENT '当前年费金额', `late_fee` varchar(20) DEFAULT NULL COMMENT '应交滞纳金额', `total` varchar(20) DEFAULT NULL COMMENT '总计', `pay_status` int(5) DEFAULT NULL COMMENT '缴费状态', `create_time` datetime DEFAULT NULL COMMENT '插入时间', `updata_time` datetime DEFAULT NULL COMMENT '更改时间', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;"
    cur.execute(create_sql_overdue)
    conn.commit()

    create_sql_refund = f"""CREATE TABLE IF
    NOT EXISTS `refund_fee_{now_time}` (
        `id` INT ( 11 ) NOT NULL AUTO_INCREMENT COMMENT '自增id',
        `refund_category` VARCHAR ( 50 ) DEFAULT NULL COMMENT '退费种类',
        `refund_amount` VARCHAR ( 50 ) DEFAULT NULL COMMENT '退费金额',
        `refund_date` datetime DEFAULT NULL COMMENT '退费日期',
        `name_payer` VARCHAR ( 50 ) DEFAULT NULL COMMENT '收款人姓名',
        `note_the_code` VARCHAR ( 50 ) DEFAULT NULL COMMENT '票据代码',
        `ticket_number` VARCHAR ( 50 ) DEFAULT NULL COMMENT '票据号码',
        `create_time` datetime DEFAULT NULL COMMENT '插入时间',
        `updata_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更改时间',
    `application_number` VARCHAR ( 50 ) DEFAULT NULL COMMENT '申请号',
    PRIMARY KEY ( `id` )) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;"""
    cur.execute(create_sql_refund)
    conn.commit()

    create_sql_chonghong = f"""CREATE TABLE IF
    NOT EXISTS `chonghong_fee_{now_time}` (
        `id` INT ( 11 ) NOT NULL AUTO_INCREMENT COMMENT '自增id',
        `chonghongqjfzlmc` VARCHAR ( 50 ) DEFAULT NULL COMMENT '冲红前费用种类',
        `chonghongqjfje` VARCHAR ( 50 ) DEFAULT NULL COMMENT '冲红前缴费金额',
        `chonghongqpjdm` VARCHAR ( 50 ) DEFAULT NULL COMMENT '冲红前票据代码',
        `chonghongqpjhm` VARCHAR ( 50 ) DEFAULT NULL COMMENT '冲红前票据号码',
            `chonghonghjfzlmc` VARCHAR ( 50 ) DEFAULT NULL COMMENT '冲红后费用种类',
        `chonghonghjfje` VARCHAR ( 50 ) DEFAULT NULL COMMENT '冲红后缴费金额',
        `chonghonghpjdm` VARCHAR ( 50 ) DEFAULT NULL COMMENT '冲红后票据代码',
        `chonghonghpjhm` VARCHAR ( 50 ) DEFAULT NULL COMMENT '冲红后票据号码',
        `chonghongsj` datetime DEFAULT NULL COMMENT '冲红日期',
        `create_time` datetime DEFAULT NULL COMMENT '插入时间',
        `updata_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更改时间',
    `application_number` VARCHAR ( 50 ) DEFAULT NULL COMMENT '申请号',
    PRIMARY KEY ( `id` )) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;"""
    cur.execute(create_sql_chonghong)
    conn.commit()


def get_patent_pay(patent_pay_res_json, application_number, inst):
    # 抓取费用信息
    code = patent_pay_res_json.get('code')
    print(f'@@{application_number}请求返回状态：{code}@@')
    patent_pay_data = patent_pay_res_json.get('data')
    # # 已缴费信息
    # yijiaofei_data = patent_pay_data.get('yijiaofei')
    # yijiaofei_list = yijiaofei_data.get('svYijfList')
    # yijiaofei_name_set = set()
    # for rows in yijiaofei_list:
    #     yijiaofei_name_set.add(rows.get('yijiaofjfzlmc'))
    #     if rows:
    #         yijiao_sql = f'''insert into by_payment_information_{now_time}(cost_category, payment_amount, pay_date, name_payer, note_the_code, ticket_number, create_time, application_number)
    #             values('{rows.get('yijiaofjfzlmc')}', '{rows.get('yijiaofjfje')}', '{rows.get('yijiaofjfrq')}', '{rows.get('yijiaofjfrxm')}', '{rows.get('yijiaofpjdm')}', '{rows.get('yijiaofpjhm')}', NOW(), '{application_number}')'''
    #         try:
    #             cur = conn.cursor()
    #             cur.execute(yijiao_sql)
    #             conn.commit()
    #             print(yijiao_sql)
    #         except:
    #             time.sleep(5)
    #             cur = conn.cursor()
    #             cur.execute(yijiao_sql)
    #             conn.commit()
    #         print(yijiao_sql)
    # print('##application_number:%s--已缴费信息入库完成##' % application_number)

    # # 应缴费信息
    # yinjiaofei_data = patent_pay_data.get('yingjiaofei')
    # yinjiaofei_list = yinjiaofei_data.get('svYingjfList')
    # for rows in yinjiaofei_list:
    #     if rows:
    #         if rows.get('yingjiaoffyzlmc') not in yijiaofei_name_set:
    #             yinjiao_sql = f'''insert into annual_fee_information_{now_time} (application_number, money_type, money, close_date, pay_status, created_at)
    #           values('{application_number}', '{rows.get('yingjiaoffyzlmc')}', '{rows.get('yingjiaoje')}', '{rows.get('jiaofeijzr')}', '{rows.get('yingjiaoffyzt')}', NOW())'''
    #             try:
    #                 cur = conn.cursor()
    #                 cur.execute(yinjiao_sql)
    #                 conn.commit()
    #             except:
    #                 time.sleep(5)
    #                 cur = conn.cursor()
    #                 cur.execute(yinjiao_sql)
    #                 conn.commit()
    #             print(yinjiao_sql)
    # print('@@application_number:%s--应缴费信息入库完成@@' % application_number)

    # # 滞纳费信息
    # zhinafei_data = patent_pay_data.get('zhinajin')
    # zhinafei_list = zhinafei_data.get('svZnjList')
    # for rows in zhinafei_list:
    #     if rows:
    #         date_str = rows.get('zhinajjfsj')
    #         date_list = date_str.split('到')
    #         date_list = [item.replace(
    #             '年', '-').replace('月', '-').replace('日', '') for item in date_list]
    #         start_date = date_list[0]
    #         end_date = date_list[1]
    #         zhinajin_sql = f'''insert into overdue_fine_{now_time}(application_number, payment_start_time, payment_end_time, current_annual_fee, late_fee, total, create_time, updata_time) values('{application_number}','{start_date}','{end_date}','{rows.get('zhinajdqnfje')}','{rows.get('zhinajyjznje')}','{rows.get('zhinajzj')}',NOW(),NOW())'''
    #         try:
    #             cur = conn.cursor()
    #             cur.execute(zhinajin_sql)
    #             conn.commit()
    #         except:
    #             time.sleep(5)
    #             cur = conn.cursor()
    #             cur.execute(zhinajin_sql)
    #             conn.commit()
    #         print(zhinajin_sql)
    # print('**application_number:%s--已缴费信息入库完成**' % application_number)

    # 退费信息
    refund_data = patent_pay_data.get('tuifei')
    print(refund_data)
    refund_list = refund_data.get('svtfList')
    print(refund_list)
    if refund_list:
        for rows in refund_list:
            refund_date = rows.get('tuifeitfrq')
            refund_fee = rows.get('tuifeitfje')
            refund_name = rows.get('tuifeitfzlmc')
            refund_xm = rows.get('tuifeiskrxm')
            tuifeipjdm = rows.get('tuifeipjdm')
            tuifeipjhm = rows.get('tuifeipjhm')
            refund_sql = f'''insert into refund_fee_{now_time}(application_number,refund_category, refund_amount, refund_date, name_payer, note_the_code, ticket_number, create_time, updata_time) values('{application_number}','{refund_name}','{refund_fee}','{refund_date}','{refund_xm}','{tuifeipjdm}','{tuifeipjhm}',NOW(),NOW())'''
            try:
                cur = conn.cursor()
                cur.execute(refund_sql)
                conn.commit()
            except:
                time.sleep(5)
                cur = conn.cursor()
                cur.execute(refund_sql)
                conn.commit()
            print(refund_sql)
    print('**application_number:%s--退费信息入库完成**' % application_number)

    # # 冲红信息
    # chonghong_data = patent_pay_data.get('chonghong')
    # chonghong_list = zhinafei_data.get('svchList')
    # if chonghong_list:
    #     for rows in chonghong_list:
    #         chonghongqjfzlmc = rows.get('chonghongqjfzlmc')
    #         chonghongqjfje = rows.get('chonghongqjfje')
    #         chonghongqpjdm = rows.get('chonghongqpjdm')
    #         chonghongqpjhm = rows.get('chonghongqpjhm')
    #         chonghonghjfzlmc = rows.get('chonghonghjfzlmc')
    #         chonghonghjfje = rows.get('chonghonghjfje')
    #         chonghonghpjdm = rows.get('chonghonghpjdm')
    #         chonghonghpjhm = rows.get('chonghonghpjhm')
    #         chonghongsj = rows.get('chonghongsj')
    #         chonhon_sql = f'''insert into chonghong_fee_{now_time}(application_number,chonghongqjfzlmc, chonghongqjfje, chonghongqpjdm, chonghongqpjhm, chonghonghjfzlmc, chonghonghjfje,chonghonghpjdm,chonghonghpjhm,chonghongsj, create_time, updata_time) values('{application_number},{chonghongqjfzlmc}','{chonghongqjfje}','{chonghongqpjdm}','{chonghongqpjhm}','{chonghonghjfzlmc}','{chonghonghjfje}','{chonghonghpjdm}','{chonghonghpjhm}','{chonghongsj}',NOW(),NOW())'''
    #         try:
    #             cur = conn.cursor()
    #             cur.execute(chonhon_sql)
    #             conn.commit()
    #         except:
    #             time.sleep(5)
    #             cur = conn.cursor()
    #             cur.execute(chonhon_sql)
    #             conn.commit()
    #         print(chonhon_sql)
    # print('**application_number:%s--冲红信息入库完成**' % application_number)



def run(i, inst, port):
    all_start_time = time.time()
    with sync_playwright() as p:
        storage_state_path = f'./auth/{inst}_state.json'
        with open(storage_state_path) as file:
            storage_state = json.load(file)
            origins = storage_state.get('origins')
            local_stroge_list = [item.get('localStorage') for item in origins]
            for local_stroge in local_stroge_list:
                for item in local_stroge:
                    if item.get('name') == 'ACCESS_TOKEN':
                        authorization = item.get('value')
        print(f'成功读取{inst}登录信息:{authorization}')
        browser = p.firefox.launch()
        context = browser.new_context(
            base_url='https://cpquery.cponline.cnipa.gov.cn', storage_state=storage_state_path)
        context.set_default_timeout(300000)
        context.set_default_navigation_timeout(300000)
        page = context.new_page()
        page.goto(
            'https://cpquery.cponline.cnipa.gov.cn/chinesepatent/index')
        page.wait_for_load_state('networkidle')
        scraped_nums = 0
        sqh_list = ['2021101677673']
        while redis_con.scard(port):
            sqh = redis_con.spop(port).replace("ZL", '')
            start_time = time.time()
            print(f'@@开始查询申请号:{sqh}_专利费用信息@@')
            api_request_context = context.request
            headers = {
                "Accept": "application/json, text/plain, */*",
                "Content-Type": "application/json;charset=utf-8",
                "Authorization": 'Bearer ' + authorization,
                "userType": "USER_RZ_DAILIJIG"
            }
            fyxx_response = api_request_context.post(
                'https://cpquery.cponline.cnipa.gov.cn/api/view/gn/fyxx', data={'zhuanlisqh': sqh}, headers=headers, timeout=0)
            patent_pay_res_json = fyxx_response.json()
            get_patent_pay(patent_pay_res_json, sqh, inst)
            end_time = time.time()
            print(f"{sqh}_本次耗时: {end_time-start_time}")
            need_time = round((end_time - start_time) *
                              redis_con.scard(port) / 60, 1)
            scraped_nums += 1
            page.reload()
    all_end_time = time.time()
    print(f"@@总耗时：{all_end_time-all_start_time}@@")


if __name__ == '__main__':
    # create_table(now_time=now_time)
    # print('@@成功创建目标表@@')
    port_dict = {'XHHG': '信合红谷接口查询', 'XH': '协和端口查询', 'WB': "tmp_退费查询"}
    inst = 'WB'
    port = port_dict.get(inst)
    run(0, inst, port)
    conn.close()
